package com.gxuwz.attend.dao.instructor;
import com.gxuwz.attend.entity.Leave;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class ExportDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 根据学期和辅导员工号查询请假记录
    public List<Leave> getLeave(String term, String instID) {
        String sql = "SELECT * \n" +
                "FROM sys_leave \n" +
                "WHERE stuNo IN (\n" +
                "    SELECT stuID \n" +
                "    FROM sys_student \n" +
                "    WHERE classID IN (\n" +
                "        SELECT classID \n" +
                "        FROM sys_classes \n" +
                "        WHERE instID = ?  -- 使用 instID 来查询辅导员\n" +
                "    ) \n" +
                "    AND courseID IN (\n" +
                "        SELECT courseID\n" +
                "        FROM sys_course \n" +
                "        WHERE term = ?  -- 添加学期过滤条件\n" +
                "    )\n" +
                "\t\t\n" +
                ")\n" +
                "AND sys_leave.status = '1';\n";
        return jdbcTemplate.query(sql, new Object[]{instID,term}, new RowMapper<Leave>() {
            @Override
            public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
                Leave leave = new Leave();
                leave.setLeaveID(rs.getString("leaveID"));
                leave.setCourseID(rs.getString("courseID"));
                leave.setReason(rs.getString("reason"));
                leave.setDaynum(rs.getInt("daynum"));
                leave.setStuNo(rs.getString("stuNo"));
                leave.setApplytime(String.valueOf(rs.getTimestamp("applytime")));
                leave.setStatus(rs.getString("status"));
                leave.setAudittime(String.valueOf(rs.getTimestamp("audittime")));
                leave.setOpinion(rs.getString("opinion"));
                return leave;
            }
        });
    }
}
